﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using IFRS.Models;

namespace IFRS.DataAccess.DAL
{
    public class SPR_DISCOUNTLOAN
    {
        public DataTable SEARCH_ALL(DiscountLoan discountLoan, Paging paging, bool isAllBranch, string sCostCenter, ref int iRecordCount)
        {
            DataTable dt;
            var utility = new IFRS_Utility();
            var lstParams = new List<SqlParameter>();
            try
            {
                lstParams.Add(new SqlParameter("@RecordCount", SqlDbType.Int));
                lstParams[0].Direction = ParameterDirection.Output;
                lstParams.Add(new SqlParameter("@PageIndex", paging.PageIndex.AsDBValue()));
                lstParams.Add(new SqlParameter("@PageSize", paging.PageSize.AsDBValue()));
                lstParams.Add(new SqlParameter("@Sorting", paging.Sorting.AsDBValue()));

                lstParams.Add(new SqlParameter("@isAllBranch", isAllBranch.AsDBValue()));
                lstParams.Add(new SqlParameter("@sCostCenter", sCostCenter.AsDBValue()));

                lstParams.Add(new SqlParameter("@TransactionDate", discountLoan.TransactionDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@AccountNo", discountLoan.AccountNo.AsDBValue()));
                lstParams.Add(new SqlParameter("@CostCenter", discountLoan.CostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@IsConfirmed", discountLoan.IsConfirmed.AsDBValue()));

                dt = utility.StoredProcDataTable("SPR_DISCOUNTLOAN_SEARCHALL", lstParams.ToArray());

                iRecordCount = (int)lstParams[0].Value;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dt;  
        }

        public DataTable SEARCH_BYID(int discountLoanId)
        {
            DataTable dt;
            var utility = new IFRS_Utility();
            var lstParams = new List<SqlParameter>();
            try
            {
                lstParams.Add(new SqlParameter("@DiscountLoanID", discountLoanId));
                dt = utility.StoredProcDataTable("SPR_DISCOUNTLOAN_SEARCHBYID", lstParams.ToArray());

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dt;
        }

        public int INSERT(DiscountLoan discountLoan)
        {
            int iNum;
            var utility = new IFRS_Utility();
            var lstParams = new List<SqlParameter>();
            try
            {
                lstParams.Add(new SqlParameter("@DiscountLoanID", SqlDbType.Int));
                lstParams[0].Direction = ParameterDirection.Output;

                lstParams.Add(new SqlParameter("@TransactionDate", discountLoan.TransactionDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@AccountNo", discountLoan.AccountNo.AsDBValue()));
                lstParams.Add(new SqlParameter("@GLAccount", discountLoan.GLAccount.AsDBValue()));
                lstParams.Add(new SqlParameter("@CostCenter", discountLoan.CostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@ContractStartDate", discountLoan.ContractStartDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@ContractEndDate", discountLoan.ContractEndDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@PaymentConditionID", discountLoan.PaymentConditionID.AsDBValue()));
                lstParams.Add(new SqlParameter("@CurrencyCode", discountLoan.CurrencyCode.AsDBValue()));
                lstParams.Add(new SqlParameter("@PreReceivedInterest", discountLoan.PreReceivedInterest.AsDBValue()));
                lstParams.Add(new SqlParameter("@LoanAmount", discountLoan.LoanAmount.AsDBValue()));
                lstParams.Add(new SqlParameter("@CustomerID", discountLoan.CustomerID.AsDBValue()));
                lstParams.Add(new SqlParameter("@ContractLoanTypeID", discountLoan.ContractLoanTypeID.AsDBValue()));
                lstParams.Add(new SqlParameter("@LoanObjectiveID", discountLoan.LoanObjectiveID.AsDBValue()));
                lstParams.Add(new SqlParameter("@MisPaymentDate", discountLoan.MisPaymentDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@ApprovedAmount", discountLoan.ApprovedAmount.AsDBValue()));
                lstParams.Add(new SqlParameter("@ReservedAmount", discountLoan.ReservedAmount.AsDBValue()));
                lstParams.Add(new SqlParameter("@CollateralAmount", discountLoan.CollateralAmount.AsDBValue()));
                lstParams.Add(new SqlParameter("@DebtAdjustFlag", discountLoan.DebtAdjustFlag.AsDBValue()));
                lstParams.Add(new SqlParameter("@IsConfirmed", discountLoan.IsConfirmed.AsDBValue()));
                lstParams.Add(new SqlParameter("@RP_GSB_PRDCD", discountLoan.RP_GSB_PRDCD.AsDBValue()));
                lstParams.Add(new SqlParameter("@RP_SOURCE_FILE", discountLoan.RP_SOURCE_FILE.AsDBValue()));
                lstParams.Add(new SqlParameter("@RP_MAPPING_CD", discountLoan.RP_MAPPING_CD.AsDBValue()));
                lstParams.Add(new SqlParameter("@RP_GSB_ASSTCLS", discountLoan.RP_GSB_ASSTCLS.AsDBValue()));
                lstParams.Add(new SqlParameter("@CreatedDate", discountLoan.CreatedDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@CreatedBy", discountLoan.CreatedBy.AsDBValue()));
                lstParams.Add(new SqlParameter("@CreatedCostCenter", discountLoan.CreatedCostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@CreatedBranch", discountLoan.CreatedBranch.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedDate", discountLoan.UpdatedDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBy", discountLoan.UpdatedBy.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedCostCenter", discountLoan.UpdatedCostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBranch", discountLoan.UpdatedBranch.AsDBValue()));

                utility.ExecuteStoredProc("SPR_DISCOUNTLOAN_INSERT", lstParams.ToArray());

                iNum = (int)lstParams[0].Value;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return iNum;
        }

        public int UPDATE(DiscountLoan discountLoan)
        {
            int iNum;
            var utility = new IFRS_Utility();
            var lstParams = new List<SqlParameter>();
            try
            {
                lstParams.Add(new SqlParameter("@DiscountLoanID", discountLoan.DiscountLoanID.AsDBValue()));
                lstParams.Add(new SqlParameter("@TransactionDate", discountLoan.TransactionDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@AccountNo", discountLoan.AccountNo.AsDBValue()));
                lstParams.Add(new SqlParameter("@GLAccount", discountLoan.GLAccount.AsDBValue()));
                lstParams.Add(new SqlParameter("@CostCenter", discountLoan.CostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@ContractStartDate", discountLoan.ContractStartDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@ContractEndDate", discountLoan.ContractEndDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@PaymentConditionID", discountLoan.PaymentConditionID.AsDBValue()));
                lstParams.Add(new SqlParameter("@CurrencyCode", discountLoan.CurrencyCode.AsDBValue()));
                lstParams.Add(new SqlParameter("@PreReceivedInterest", discountLoan.PreReceivedInterest.AsDBValue()));
                lstParams.Add(new SqlParameter("@LoanAmount", discountLoan.LoanAmount.AsDBValue()));
                lstParams.Add(new SqlParameter("@CustomerID", discountLoan.CustomerID.AsDBValue()));
                lstParams.Add(new SqlParameter("@ContractLoanTypeID", discountLoan.ContractLoanTypeID.AsDBValue()));
                lstParams.Add(new SqlParameter("@LoanObjectiveID", discountLoan.LoanObjectiveID.AsDBValue()));
                lstParams.Add(new SqlParameter("@MisPaymentDate", discountLoan.MisPaymentDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@ApprovedAmount", discountLoan.ApprovedAmount.AsDBValue()));
                lstParams.Add(new SqlParameter("@ReservedAmount", discountLoan.ReservedAmount.AsDBValue()));
                lstParams.Add(new SqlParameter("@CollateralAmount", discountLoan.CollateralAmount.AsDBValue()));
                lstParams.Add(new SqlParameter("@DebtAdjustFlag", discountLoan.DebtAdjustFlag.AsDBValue()));
                lstParams.Add(new SqlParameter("@IsConfirmed", discountLoan.IsConfirmed.AsDBValue()));
                lstParams.Add(new SqlParameter("@RP_GSB_PRDCD", discountLoan.RP_GSB_PRDCD.AsDBValue()));
                lstParams.Add(new SqlParameter("@RP_SOURCE_FILE", discountLoan.RP_SOURCE_FILE.AsDBValue()));
                lstParams.Add(new SqlParameter("@RP_MAPPING_CD", discountLoan.RP_MAPPING_CD.AsDBValue()));
                lstParams.Add(new SqlParameter("@RP_GSB_ASSTCLS", discountLoan.RP_GSB_ASSTCLS.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedDate", discountLoan.UpdatedDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBy", discountLoan.UpdatedBy.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedCostCenter", discountLoan.UpdatedCostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBranch", discountLoan.UpdatedBranch.AsDBValue()));

                iNum = (int)utility.ExecuteStoredProc("SPR_DISCOUNTLOAN_UPDATE", lstParams.ToArray());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return iNum;
        }

        public int DELETE(DiscountLoan discountLoan)
        {
            int iNum;
            var utility = new IFRS_Utility();
            var lstParams = new List<SqlParameter>();
            try
            {
                lstParams.Add(new SqlParameter("@DiscountLoanID", discountLoan.DiscountLoanID.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedDate", discountLoan.UpdatedDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBy", discountLoan.UpdatedBy.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedCostCenter", discountLoan.UpdatedCostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBranch", discountLoan.UpdatedBranch.AsDBValue()));

                iNum = (int)utility.ExecuteStoredProc("SPR_DISCOUNTLOAN_DELETE", lstParams.ToArray());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return iNum;
        }
    }
}
